package com.zx.article.article.dao.impl;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.zx.article.article.dao.IArticleDao;
import com.zx.article.article.model.ArticleModel;
import com.zx.article.util.DBUtils;

public class ArticleDaoImpl implements IArticleDao {

	private DBUtils dbUtils = new DBUtils();

	@Override
	public String insert(ArticleModel articleModel) {
		String sqlString = "INSERT INTO article(article.a_title,article.a_key,article.a_type_id,article.a_writer_account,a_file_name,article.a_url,article.a_message,article.a_time) VALUES(?,?,?,?,?,?,?,?)";
		Object[] valObjects = { articleModel.getTitle(), articleModel.getKey(), articleModel.getTypeId(),
				articleModel.getWritterAccount(), articleModel.getFileName(), articleModel.getUrl(),
				articleModel.getMessage(), articleModel.getTime() };
		return dbUtils.update(sqlString, valObjects);
	}

	private Map<String, String> getMap() {
		Map<String, String> map = new HashMap<>();
		map.put("id", "a_id");
		map.put("title", "a_title");
		map.put("key", "a_key");
		map.put("typeId", "a_type_id");
		map.put("WritterAccount", "a_writer_account");
		map.put("fileName", "a_file_name");
		map.put("url", "a_url");
		map.put("message", "a_message");
		map.put("time", "a_time");
		return map;
	}

	@Override
	public int getCount(ArticleModel articleModel) {
		StringBuffer sqlString = new StringBuffer(
				"SELECT COUNT(1) FROM article WHERE article.a_writer_account IN (SELECT writer.w_account FROM writer WHERE writer.w_pen_name LIKE ?) AND article.a_title LIKE ? AND article.a_key LIKE ? ");
		List<Object> list = new ArrayList<>(
				Arrays.asList(articleModel.getAuthor(), articleModel.getTitle(), articleModel.getKey()));
		if (articleModel.getTypeId() != null) {
			sqlString.append(" AND article.a_type_id LIKE ? ");
			list.add(articleModel.getTypeId());
		}
		if ("pass".equals(articleModel.getResult())) {
			sqlString.append(
					"AND article.a_writer_account IN (SELECT r_article_id FROM review_records WHERE r_result = 'pass') ");
		} else if ("notpass".equals(articleModel.getResult())) {
			sqlString.append(
					"AND article.a_writer_account IN (SELECT r_article_id FROM review_records WHERE r_result = 'notpass') AND article.a_writer_account NOT IN (SELECT r_article_id FROM review_records WHERE r_result = 'pass')");
		} else if ("notfind".equals(articleModel.getResult())) {
			sqlString.append(
					"AND article.a_writer_account NOT IN (SELECT r_article_id FROM review_records WHERE r_result IN ('notpass','pass'))");
		}
		return dbUtils.queryCount(sqlString.toString(), list.toArray());
	}

	private List<Object> appendWhere(StringBuffer sqlBuffer, ArticleModel articleModel) {
		List<Object> list = new ArrayList<>();
		sqlBuffer.append("where 1=1 ");
		String title = articleModel.getTitle();
		if (title != null && !title.isEmpty()) {
			sqlBuffer.append("and a_title like ? ");
			list.add(title);
		}
		String key = articleModel.getKey();
		if (key != null && !key.isEmpty()) {
			sqlBuffer.append("and a_key like ? ");
			list.add(key);
		}
		Integer typeId = articleModel.getTypeId();
		if (typeId != null) {
			sqlBuffer.append("and a_type_id = ? ");
			list.add(typeId);
		}
		sqlBuffer.append("HAVING 1=1 ");
		String author = articleModel.getAuthor();
		if (author != null && !author.isEmpty()) {
			sqlBuffer.append("and author like ? ");
			list.add(author);
		}
		String result = articleModel.getResult();
		if ("pass".equals(result)) {
			sqlBuffer.append("AND result = 'pass' ");
		} else if ("notpass".equals(result)) {
			sqlBuffer.append("AND result <> 'pass' AND result IS NOT NULL ");
		} else if ("notfind".equals(result)) {
			sqlBuffer.append("AND result IS NULL ");
		}
		if (articleModel.isPageOn()) {
			sqlBuffer.append("limit ?,? ");
			list.add(articleModel.getRowStart());
			list.add(articleModel.getRowCount());
		}
		return list;
	}

	@Override
	public List<ArticleModel> getList(ArticleModel articleModel) {
		StringBuffer sqlBuffer = new StringBuffer(
				"SELECT article.a_id,article.a_title,article.a_key,article.a_type_id,article.a_writer_account,article.a_file_name,article.a_url,article.a_message,article.a_time,(SELECT w_pen_name FROM writer WHERE w_account = a_writer_account) AS author,(SELECT article_type.at_name FROM article_type WHERE article.a_type_id = article_type.at_id) AS type,(SELECT DISTINCT review_records.r_result FROM review_records WHERE review_records.r_article_id = article.a_id) AS result FROM article ");
		List<Object> appendWhere = appendWhere(sqlBuffer, articleModel);
		Map<String, String> map = getMap();
		map.put("author", "author");
		map.put("type", "type");
		map.put("result", "result");
		return dbUtils.queryList(sqlBuffer.toString(), appendWhere.toArray(), ArticleModel.class, map);
	}

	@Override
	public String delete(Integer id) {
		String sqlString = "DELETE FROM article WHERE a_id = ? ";
		Object[] valObjects = { id };
		return dbUtils.update(sqlString, valObjects);
	}

	@Override
	public ArticleModel queryOne(Integer id) {
		String sqlString = "SELECT * FROM article WHERE a_id = ? ";
		Object[] valObjects = { id };
		return dbUtils.queryOne(sqlString, valObjects, ArticleModel.class, getMap());
	}

	@Override
	public String updata(ArticleModel articleModel) {
		StringBuffer sqlString = new StringBuffer("UPDATE article ");
		List<Object> appendSet = appendSet(sqlString, articleModel);
		System.out.println(sqlString.toString());
		return dbUtils.update(sqlString.toString(), appendSet.toArray());
	}

	private List<Object> appendSet(StringBuffer sqlBuffer, ArticleModel articleModel) {
		List<Object> list = new ArrayList<>();
		sqlBuffer.append("set a_id = a_id ");
		String title = articleModel.getTitle();
		if (title != null && !title.isEmpty()) {
			sqlBuffer.append(", a_title = ? ");
			list.add(title);
		}
		String key = articleModel.getKey();
		if (key != null && !key.isEmpty()) {
			sqlBuffer.append(", a_key = ? ");
			list.add(key);
		}
		Integer typeId = articleModel.getTypeId();
		if (typeId != null) {
			sqlBuffer.append(", a_type_id = ? ");
			list.add(typeId);
		}
		String writterAccount = articleModel.getWritterAccount();
		if (writterAccount != null && !writterAccount.isEmpty()) {
			sqlBuffer.append(", a_writer_account= ? ");
			list.add(writterAccount);
		}
		String fileName = articleModel.getFileName();
		if (fileName != null && !fileName.isEmpty()) {
			sqlBuffer.append(", a_file_name= ? ");
			list.add(fileName);
		}
		String url = articleModel.getUrl();
		if (url != null && !url.isEmpty()) {
			sqlBuffer.append(", a_url = ? ");
			list.add(url);
		}
		String message = articleModel.getMessage();
		if (message != null && !message.isEmpty()) {
			sqlBuffer.append(", a_message = ? ");
			list.add(message);
		}
		String time = articleModel.getTime();
		if (time != null && !time.isEmpty()) {
			sqlBuffer.append(", a_time = ? ");
			list.add(time);
		}
		BigDecimal wordNum = articleModel.getWordNum();
		if (wordNum != null) {
			sqlBuffer.append(", a_num = ? ");
			list.add(wordNum);
		}
		sqlBuffer.append("where a_id = ? ");
		list.add(articleModel.getId());
		return list;
	}

	private List<Object> appendCaseWhere(StringBuffer sqlBuffer, ArticleModel articleModel) {
		List<Object> list = new ArrayList<>();
		String title = articleModel.getTitle();
		if (title != null && !title.isEmpty()) {
			sqlBuffer.append("and a_title like ? ");
			list.add(title);
		}
		String author = articleModel.getAuthor();
		if (author != null && !author.isEmpty()) {
			sqlBuffer.append("and author like ? ");
			list.add(author);
		}
		String type = articleModel.getType();
		if (type != null && !type.isEmpty()) {
			sqlBuffer.append("and type like ? ");
			list.add(type);
		}
		return list;
	}

	@Override
	public int getCaseCount(ArticleModel articleModel) {
		StringBuffer sqlString = new StringBuffer(
				"SELECT COUNT(1) FROM (SELECT a_title,type,author FROM (SELECT a_title,( SELECT at_name FROM article_type WHERE article_type.at_id = article.a_type_id) AS type,( SELECT writer.w_pen_name FROM writer WHERE writer.w_account = article.a_writer_account) AS author FROM article WHERE a_id IN ( SELECT r_article_id FROM review_records WHERE r_result = 'pass')) AS articles LEFT JOIN type_pay ON articles.type = type_pay.type_id) AS re where 1=1 ");
		List<Object> appendCaseWhere = appendCaseWhere(sqlString, articleModel);
		return dbUtils.queryCount(sqlString.toString(), appendCaseWhere.toArray());
	}

	@Override
	public List<ArticleModel> getCaseList(ArticleModel articleModel) {
		StringBuffer sqlBuffer = new StringBuffer(
				"SELECT a_id,a_title,type,author,(a_num*type_pay.article_pay) AS article_pay,type_pay.check_pay,type_pay.page_pay FROM (SELECT a_id,a_title,( SELECT at_name FROM article_type WHERE article_type.at_id = article.a_type_id) AS type,( SELECT writer.w_pen_name FROM writer WHERE writer.w_account = article.a_writer_account) AS author,a_num FROM article WHERE a_id IN ( SELECT r_article_id FROM review_records WHERE r_result = 'pass')) AS articles LEFT JOIN type_pay ON articles.type = type_pay.type_id where 1 = 1 ");
		List<Object> appendCaseWhere = appendCaseWhere(sqlBuffer, articleModel);
		Map<String, String> map = new HashMap<>();
		map.put("id", "a_id");
		map.put("title", "a_title");
		map.put("type", "type");
		map.put("author", "author");
		map.put("article", "article_pay");
		map.put("check", "check_pay");
		map.put("page", "page_pay");
		return dbUtils.queryList(sqlBuffer.toString(), appendCaseWhere.toArray(), ArticleModel.class, map);
	}
}
